/**
 * Copyright (c) 2020 铭软科技(mingsoft.net)
 * 本软件及相关文档文件（以下简称“软件”）的版权归 铭软科技 所有
 * 遵循铭软科技《保密协议》
 */
package net.mingsoft.mwebsite.biz.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.HexUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Db;
import cn.hutool.db.Entity;
import cn.hutool.db.PageResult;
import net.mingsoft.basic.exception.BusinessException;
import net.mingsoft.basic.util.BasicUtil;
import net.mingsoft.mdiy.dao.IModelDao;
import net.mingsoft.mdiy.entity.ModelEntity;
import net.mingsoft.mwebsite.biz.IBackUpBiz;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.core.io.Resource;
import org.springframework.core.io.UrlResource;
import org.springframework.jdbc.datasource.init.ScriptUtils;
import org.springframework.messaging.simp.SimpMessagingTemplate;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.io.BufferedWriter;
import java.io.File;
import java.io.IOException;
import java.math.BigDecimal;
import java.net.MalformedURLException;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

@Service
public class BackUpBizImpl implements IBackUpBiz {

    /*
     * log4j日志记录
     */
    protected final Logger LOG = LoggerFactory.getLogger(this.getClass());

    @Autowired
    private DataSource dataSource;

    /*
     * 注入modelDao，用于备份站群的自定义模型
     */
    @Autowired
    private IModelDao modelDao;

    @Value("${ms.website.backup:/sql-bak}")
    private String websiteBackUp;
    @Override
    public void backupAllTable() {
        File file = FileUtil.file(BasicUtil.getRealPath(websiteBackUp), DateUtil.format(new Date(), "yyyy-MM-dd HH-mm-ss") + ".sql");
        BufferedWriter sqlFile = FileUtil.getWriter(file, "UTF-8",true);
        try {
            sqlFile.write("SET FOREIGN_KEY_CHECKS=0;\r\n");
            String databaseName = getDatabaseName();
            List<String> tables = getTables(databaseName);
            for (String table :
                    tables) {
                sqlFile.write(StrUtil.format("\n-- ----------------------------\n" +
                        "-- Table structure for {}\n" +
                        "-- ----------------------------\n",table));
                writerTableBackUpSql(table,sqlFile);
            }
            sqlFile.close();
            LOG.info("备份完成");
        } catch (Exception e) {
            cathFile(file, sqlFile);
            LOG.error("导出出错",e);
            throw new BusinessException("备份失败");
        }

    }

    @Override
    public void backupSiteTable(Integer appId) {
      File file = FileUtil.file(BasicUtil.getRealPath(websiteBackUp), appId + "", DateUtil.format(new Date(), "yyyy-MM-dd HH-mm-ss") + ".sql");
      BufferedWriter sqlFile = FileUtil.getWriter(file, "UTF-8",true);
      //查询自定义模型创建的表
      ModelEntity diyModel = new ModelEntity();
      List<ModelEntity> modelEntityList = modelDao.query(diyModel);
      List<String> modelTableNames = new ArrayList<>();
      for (ModelEntity model : modelEntityList) {
          if(StringUtils.isNotBlank(model.getModelTableName())) {
              modelTableNames.add(model.getModelTableName());
          }

      }
      try {
            List<String> tables = CollUtil.toList("cms_category","cms_content","mdiy_dict","mdiy_model");
            for (String table :
                    tables) {
                sqlFile.write(StrUtil.format("\n-- ----------------------------\n" +
                        "-- Table structure for {}\n" +
                        "-- ----------------------------\n",table));
                //查询指定站点下的数据
                Entity entity = Entity.create(table).set("app_id", appId);
                writerPage(table, sqlFile, entity);
            }
            //备份自定义模型下的业务表
              for (String table :
                      modelTableNames) {
                  sqlFile.write(StrUtil.format("\n-- ----------------------------\n" +
                          "-- Table structure for {}\n" +
                          "-- ----------------------------\n",table));
                  writerTableBackUpSql(table, sqlFile);
              }
            sqlFile.close();
          LOG.info("备份完成");
        } catch (Exception e) {
          cathFile(file, sqlFile);
          LOG.error("导出出错",e);
            throw new BusinessException("备份失败");
        }

    }

    /**
     * 处理失败后的文件 执行删除
     * @param file
     * @param sqlFile
     */
    private void cathFile(File file, BufferedWriter sqlFile) {
        if (sqlFile != null) {
            try {
                sqlFile.close();
            } catch (IOException ex) {
                ex.printStackTrace();
            }
        }
        if (file.exists()) {
            file.delete();
        }
    }

    @Override
    public List<Map<String, String>> getSqlFileList() {
        //获取文件路径
        String path = FileUtil.file(BasicUtil.getRealPath(websiteBackUp)).getPath();
        if (!FileUtil.exist(FileUtil.file(path))) {
            FileUtil.mkdir(path);
        }
       // 获取文件夹下子文件所有文件名
        List<String> files = FileUtil.listFileNames(path);
        //反转数组
        CollUtil.reverse(files);
        List<Map<String, String>> list = new ArrayList<>();
        for (String fileName : files) {
            Map<String, String> map = new HashMap<>();
            map.put("sqlFile", fileName);
            //运算转换单位（保留2位小数） 转换单位为MB
            double fileSize = FileUtil.file(path, fileName).length();
            double fileSizeMB = new BigDecimal(fileSize/1024/1024).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
            map.put("fileSize", fileSizeMB + "");
            list.add(map);
        }
        return list;
    }

    @Override
    public void rollBack(String file) throws SQLException, MalformedURLException {
        File sqlFile = FileUtil.file(BasicUtil.getRealPath(websiteBackUp), file);
        if (!sqlFile.exists()) {
            LOG.info("文件不存在");
            return;
        }
        Resource resource = new UrlResource(sqlFile.toURI());
        LOG.info("开始回滚");
        ScriptUtils.executeSqlScript(dataSource.getConnection(), resource);
        LOG.info("回滚成功");
    }

    /**
     * 获取数据库名称
     *
     * @return
     */
    private String getDatabaseName() throws SQLException {
        String databaseName = Db.use(dataSource).queryString("select database()");
        return databaseName;
    }

    /**
     * 获取数据库所有表名
     *
     * @param databaseName
     * @return
     * @throws SQLException
     */
    private List<String> getTables(String databaseName) throws SQLException {
        return Db.use(dataSource).query(StrUtil.format("SHOW TABLES FROM `{}`", databaseName), String.class);
    }

    /**
     * 获取表的备份sql并写入
     */
    private void writerTableBackUpSql(String table, BufferedWriter sqlFile ) throws SQLException, IOException {
        LOG.info("备份表{}",table);
        //移除表sql
        String drop = StrUtil.format("DROP TABLE IF EXISTS `{}`;\n", table);
        sqlFile.write(drop);
        //创建表sql
        String ddl = Db.use(dataSource).queryOne(StrUtil.format("show create table `{}`", table)).getStr("Create Table")+";";
        sqlFile.write(ddl);
        //添加注释
        sqlFile.write(StrUtil.format("\n" +
                "-- ----------------------------\n" +
                "-- Records of {}\n" +
                "-- ----------------------------\n",table));
        Entity entity = Entity.create(table);
        writerPage(table, sqlFile, entity);
    }

    /**
     * 分页写入
     * @param table 表名
     * @param sqlFile 文件
     * @param entity
     * @throws SQLException
     */
    private void writerPage(String table, BufferedWriter sqlFile, Entity entity) throws SQLException, IOException {
        LOG.info("备份表记录{}",table);
        //移除表sql
//        String drop = StrUtil.format("DROP TABLE IF EXISTS `{}`;\n", table);
//        sqlFile.write(drop);
        //创建表sql
//        String ddl = Db.use(dataSource).queryOne(StrUtil.format("show create table `{}`", table)).getStr("Create Table")+";";
//        sqlFile.write(ddl);
        //添加注释
        sqlFile.write(StrUtil.format("\n" +
                "-- ----------------------------\n" +
                "-- Records of {}\n" +
                "-- ----------------------------\n",table));
        int pageSize = 100;
        PageResult<Entity> page = Db.use(dataSource).page(entity, -1, pageSize);
        writerDataSqlList(table, page,sqlFile);
        for (int i = 1; i <= page.getTotalPage(); i++) {
           page = Db.use(dataSource).page(entity, i, pageSize);
           writerDataSqlList(table, page,sqlFile);
        }
    }

    /**
     * 数据拼接INSERT INTO语句并写入
     * @param table 表面
     * @param dataList 数据集合
     * @return
     */
    private  void writerDataSqlList(String table, List<Entity> dataList, BufferedWriter sqlFile) {
        //数据集合
         dataList.stream().forEach(data -> {
            Set<String> fieldNames = data.getFieldNames();
            Object[] values = data.values().stream().toArray();
             try {
                 sqlFile.write(  StrUtil.format("INSERT INTO `{}` (`{}`) VALUES ({});\r\n", table, CollUtil.join(fieldNames,"`,`"), getPreparedSQL(values)));
                 sqlFile.flush();
             } catch (IOException e) {
                 e.printStackTrace();
             }
         });
    }

    /**
     * 获得values类型转换返回逗号分隔字符串
     *
     * @param values
     * @return
     */
    private String getPreparedSQL(Object... values) {
        List<Object> objects=new ArrayList<>();
        for (Object param:
        values) {
            if (param instanceof Number) {
                objects.add(param);
            }else if (param instanceof String) {
                //转义
                String p = param.toString()
                        .replaceAll("\\\\", "\\\\\\\\")
                        .replaceAll("\r\n", "\\\\r\\\\n")
                        .replaceAll("\n", "\\\\r\\\\n")
                        .replaceAll("'", "\\\\\\'")
                        .replaceAll("\"", "\\\\\"");
                objects.add(StrUtil.format("'{}'",p));
            }else if (param instanceof Timestamp){
                objects.add(StrUtil.format("'{}'",param));
            }else if (param instanceof byte[]) {
                //如果是blob类型则转化成16进制字符串
                String s = HexUtil.encodeHexStr((byte[]) param);
                if (!StrUtil.isEmpty(s)) {
                    s = "0x" + s;
                }else {
                    s = "''";
                }
                objects.add(s);
            } else if (param instanceof Date){
                objects.add(StrUtil.format("'{}'",param));
            } else {
                objects.add(param);
            }
        }
        return CollUtil.join(objects,",");
    }
}
